using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using THANHTUNGO.Portal.Components.Security;

namespace ITG.Modules.HR.Components
{
    public class Accessing
    {
        private static void DeleteBy_RoleID(DbTransaction transaction, int roleID)
        {
            string query = "DELETE FROM t_ITG_MAPPING_Departments WHERE RoleID = " + roleID;
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetSqlStringCommand(query);
            db.ExecuteNonQuery(dbCommand, transaction);
        }

        private static void Insert(DbTransaction transaction, int roleID, int departmentID)
        {
            string query = "INSERT INTO t_ITG_MAPPING_Departments(RoleID, SB_DepartmentID) VALUES(@RoleID, @DepartmentID)";
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetSqlStringCommand(query);
            db.AddInParameter(dbCommand, "@RoleID", SqlDbType.Int, roleID);
            db.AddInParameter(dbCommand, "@DepartmentID", SqlDbType.Int, departmentID);
            db.ExecuteNonQuery(dbCommand, transaction);
        }

        public static void UpdateAccessing(int roleID, string[] departments)
        {
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection) db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    DeleteBy_RoleID(transaction, roleID);
                    foreach (string id in departments)
                    {
                        Insert(transaction, roleID, Convert.ToInt32(id));
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static string[] SelectDepartmentBy_RoleID(int roleID)
        {
            string query = "SELECT * FROM t_ITG_MAPPING_Departments WHERE RoleID = " + roleID;
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetSqlStringCommand(query);
            IDataReader reader = db.ExecuteReader(dbCommand);

            ArrayList departments = new ArrayList();
            while (reader.Read())
            {
                departments.Add(reader["SB_DepartmentID"].ToString());
            }

            reader.Close();
            dbCommand.Connection.Close();
            return (string[]) departments.ToArray(typeof (string));
        }

        public static string[] SelectDepartmentBy_UserID(int userID)
        {
            string[] roles = User.GetRoles(userID);
            string query = "SELECT DISTINCT SB_DepartmentID FROM t_ITG_MAPPING_Departments ";
            if (roles.Length > 0)
            {
                query += "WHERE ";
                foreach (string role in roles)
                {
                    query += string.Format("RoleID = {0} OR ", role);
                }
                query = query.Remove(query.Length - 4, 4);
            }
            else
            {
                query += "WHERE 1 = 2";
            }
            SqlDatabase db = (SqlDatabase) DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand) db.GetSqlStringCommand(query);
            IDataReader reader = db.ExecuteReader(dbCommand);

            ArrayList departments = new ArrayList();
            while (reader.Read())
            {
                departments.Add(reader["SB_DepartmentID"].ToString());
            }

            reader.Close();
            dbCommand.Connection.Close();
            return (string[]) departments.ToArray(typeof (string));
        }
    }
}